package com.api.shopapi.mapper;


import com.api.shopapi.entity.AccountingRecord;
import com.api.shopapi.entity.MyResult;
import com.api.shopapi.entity.OrderBusiness;
import com.api.shopapi.entity.User;
import com.api.shopapi.entity.dto.BusinessDTO1;
import com.api.shopapi.entity.dto.OrderBusinessDTO;
import com.api.shopapi.entity.dto.OrderStatisticsDTO;
import com.api.shopapi.entity.dto.UserDTO2;
import org.apache.ibatis.annotations.*;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;


@Repository
@Mapper
public interface OrderBusinessMapper {

    @Insert("insert into order_business" +
            "(" +
            "OrderBusinessCode," +
            "OrderCode," +
            "BusinessCode," +
            "BusinessName," +
            "Price," +
            "CreateTime," +
            "PayTime," +
            "Status," +
            "OrderType," +
            "PayType," +
            "SerialNumber," +
            "CurrentBalance," +
            "OrderFrom," +
            "IsDelete" +
            ")" +
            "value" +
            "(" +
            "#{orderBusiness.OrderBusinessCode}," +
            "#{orderBusiness.OrderCode}," +
            "#{orderBusiness.BusinessCode}," +
            "#{orderBusiness.BusinessName}," +
            "#{orderBusiness.Price}," +
            "#{orderBusiness.CreateTime}," +
            "#{orderBusiness.PayTime}," +
            "#{orderBusiness.Status}," +
            "#{orderBusiness.OrderType}," +
            "#{orderBusiness.PayType}," +
            "#{orderBusiness.SerialNumber}," +
            "#{orderBusiness.CurrentBalance}," +
            "#{orderBusiness.OrderFrom}," +
            "#{orderBusiness.IsDelete}" +
            ")")
    int insert(@Param("orderBusiness") OrderBusiness orderBusiness);

    @Delete("delete from order_business where OrderBusinessCode=#{orderBusinessCode}")
    int delete(@Param("orderBusinessCode") String orderBusinessCode);

    @Update("update order_business set " +
            "OrderCode = #{orderBusiness.OrderCode}," +
            "BusinessCode = #{orderBusiness.BusinessCode}," +
            "BusinessName = #{orderBusiness.BusinessName}," +
            "Price = #{orderBusiness.Price}," +
            "PayTime = #{orderBusiness.PayTime}," +
            "Status = #{orderBusiness.Status}," +
            "OrderType = #{orderBusiness.OrderType}," +
            "PayType = #{orderBusiness.PayType}," +
            "SerialNumber = #{orderBusiness.SerialNumber}," +
            "CurrentBalance = #{orderBusiness.CurrentBalance}, " +
            "CompleteTime = #{orderBusiness.CompleteTime}, " +
            "OrderFrom = #{orderBusiness.OrderFrom}, " +
            "IsDelete = #{orderBusiness.IsDelete} " +
            " where OrderBusinessCode=#{orderBusiness.OrderBusinessCode}")
    int update(@Param("orderBusiness") OrderBusiness orderBusiness);


    @Update("update order_business set IsDelete = 0 ")
    int updateIsDelete();


    @Select("select * from order_business order by CreateTime desc")
    List<OrderBusiness> getList();

    @Select("select * from order_business where OrderCode=#{orderCode} limit 1")
    OrderBusiness getByOrderCode(@Param("orderCode") String orderCode);


    @Select("select * from order_business where OrderCode=#{orderCode} ")
    List<OrderBusiness> getListByOrderCode(@Param("orderCode") String orderCode);

    @Select("select * from order_business where OrderBusinessCode=#{orderBusinessCode} limit 1")
    OrderBusiness getByOrderBusinessCode(@Param("orderBusinessCode") String orderBusinessCode);


    //老人在某商家代表下的订单列表
    @Select("select ob.* from " +
            "order_business ob join `order` o on ob.OrderCode = o.OrderCode " +
            "where " +
            "o.UserCode=#{userCode} " +
            "order by ob.CreateTime desc")
    List<OrderBusinessDTO> getListByUserCode(@Param("userCode") String userCode);

    //老人在某政府下的订单列表
    @Select("select ob.* from " +
            "order_business ob join `order` o on ob.OrderCode = o.OrderCode " +
            "where " +
            "o.UserCode=#{userCode} " +
            "and o.governmentCode=#{governmentCode} " +
            "order by ob.CreateTime desc")
    List<OrderBusinessDTO> getListByUserCodeGovernmentCode(@Param("userCode") String userCode,
                                                           @Param("governmentCode") String governmentCode);

    //收银员的订单列表
    @Select("select ob.* from " +
            " order_business ob join `order` o on ob.OrderCode = o.OrderCode " +
            " where " +
            " o.UserCode=#{userCode} " +
            " and ob.orderFrom='table' " +
            " order by ob.CreateTime desc")
    List<OrderBusinessDTO> getListByUserCodeTable(@Param("userCode") String userCode);

    //收银员某时间段的订单列表
    @Select("select ob.* from " +
            " order_business ob join `order` o on ob.OrderCode = o.OrderCode " +
            " where " +
            " o.UserCode=#{userCode} " +
            " and ob.orderFrom='table' " +
            " and (ob.CreateTime between #{startTime} and #{endTime})")
    List<OrderBusinessDTO> getListByUserCodeTableTime(@Param("userCode") String userCode,
                                                      @Param("startTime") Date startTime,
                                                      @Param("endTime") Date endTime);


    //政府下的所有商家的订单
    @Select("<script>" +
            "select ob.* from " +
            "order_business ob join `order` o " +
            "on ob.OrderCode = o.OrderCode " +
            "where " +
            "o.governmentCode=#{governmentCode} " +
            "<if test =' businessCode != \"00000000-0000-0000-0000-000000000000\" '> " +
            "and ob.businessCode = #{businessCode} " +
            "</if>" +
            "order by ob.CreateTime desc" +
            "</script>")
    List<OrderBusinessDTO> getListByGovernmentCodeBusinessCode(@Param("governmentCode") String governmentCode,
                                                               @Param("businessCode") String businessCode);


    //商家的所有订单
    @Select("select * from order_business where BusinessCode=#{businessCode} order by CreateTime desc")
    List<OrderBusinessDTO> getListByBusinessCode(@Param("businessCode") String businessCode);


    //商家的所有订单（增加状态过滤）
    @Select("select * from order_business where BusinessCode=#{businessCode} and status=#{status} order by CreateTime desc")
    List<OrderBusinessDTO> getListByBusinessCodeStatus(@Param("businessCode") String businessCode,
                                                       @Param("status") int status);

    //商家的所有订单（增加状态，时间过滤）
    @Select("select * from order_business where" +
            " BusinessCode=#{businessCode}" +
            " and Status=#{status}" +
            " and (CreateTime between #{beginTime} and #{endTime})" +
            " order by CreateTime desc")
    List<OrderBusinessDTO> getListByBusinessCodeStatusTime(@Param("businessCode") String businessCode,
                                                           @Param("status") int status,
                                                           @Param("beginTime") Date beginTime,
                                                           @Param("endTime") Date endTime);



    //服务人员的订单（服务人员已绑定社区）
    @Select("select ob.* from order_business ob " +
            "left join order_serve os " +
            "on ob.OrderBusinessCode=os.OrderBusinessCode " +
            "where os.UserCode=#{userCode}" +
            "order by ob.CreateTime desc")
    List<OrderBusinessDTO> getListByServeCode(@Param("userCode") String serveCode);


    //服务人员某时间段的订单总数
    @Select("select count(*) from " +
            "order_business ob join order_serve os " +
            "on ob.OrderBusinessCode = os.OrderBusinessCode " +
            " where ob.BusinessCode=#{businessCode}" +
            " and os.UserCode=#{userCode}" +
            " and (ob.CreateTime between #{beginTime} and #{endTime})" +
            " order by ob.CreateTime desc")
    int getCountByBusinessCodeUserCode(@Param("businessCode") String businessCode,
                                       @Param("userCode") String userCode,
                                       @Param("beginTime") Date beginTime,
                                       @Param("endTime") Date endTime);


    //服务人员某时间段的订单总数（增加状态过滤）
    @Select("select count(*) from " +
            "order_business ob join order_serve os " +
            "on ob.OrderBusinessCode = os.OrderBusinessCode " +
            " where ob.BusinessCode=#{businessCode}" +
            " and os.UserCode=#{userCode}" +
            " and (ob.CreateTime between #{beginTime} and #{endTime})" +
            " and ob.Status=#{status}" +
            " order by ob.CreateTime desc")
    int getCountByBusinessCodeUserCodeStatus(@Param("businessCode") String businessCode,
                                             @Param("userCode") String userCode,
                                             @Param("beginTime") Date beginTime,
                                             @Param("endTime") Date endTime,
                                             @Param("status") int status);



    //所有订单（居委会，街道）
    @Select("select ob.* from " +
            "order_business ob join `order` o " +
            " on ob.OrderCode = o.OrderCode " +
            "join elderly e " +
            " on o.UserCode = e.UserCode " +
            " where instr(e.ZoneCodePath,#{zoneCode}) order by ob.CreateTime desc")
    List<OrderBusinessDTO> getListByZoneCode(@Param("zoneCode") String zoneCode);





    //商家后台查看所有订单
    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join (select  OrderBusinessCode,GoodsCode from order_goods) as og  on ob.OrderBusinessCode=og.OrderBusinessCode " +
            "left join (select GoodsCode,GoodsType from goods ) as g on og.GoodsCode=g.GoodsCode " +
            "left join user u on o.UserCode = u.UserCode " +
            "where  ob.BusinessCode=#{businessCode} " +
            " and o.GovernmentCode=#{governmentCode} " +
            "and (ob.CreateTime between #{beginTime} and #{endTime}) " +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' goodsType !=-1 '>" +
            " and g.GoodsType=#{goodsType} " +
            "</if>" +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if> " +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO> getListForBusiness(@Param("businessCode") String businessCode,
                                              @Param("governmentCode") String governmentCode,
                                              @Param("beginTime") Date beginTime,
                                              @Param("endTime") Date endTime,
                                              @Param("status") int status,
                                              @Param("goodsType") int goodsType,
                                              @Param("name") String name,
                                              @Param("phone") String phone);


    //消费流水
    @Select("<script>" +
            "select * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode " +
            "where o.GovernmentCode=#{governmentCode}  " +
            "and  ob.BusinessCode=#{businessCode}  " +
            "and ob.Status in (3,7,14,16)  and  (ob.CreateTime between #{beginTime} and #{endTime}) " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "</script> ")
    List<OrderBusinessDTO> getListForWater(@Param("governmentCode") String governmentCode,
                                           @Param("businessCode") String businessCode,
                                           @Param("name") String name,
                                           @Param("phone") String phone,
                                           @Param("beginTime") Date beginTime,
                                           @Param("endTime") Date endTime);


    //服务人员后台查看订单
    @Select("<script>" +
            "select  * from  order_business as ob " +
            "left join  `order` as o on o.OrderCode=ob.OrderCode " +
            "left join  order_serve as os on ob.OrderBusinessCode=os.OrderBusinessCode " +
            "left join user u on o.UserCode = u.UserCode " +
            "where os.UserCode=#{userCode} " +
            "and (ob.CreateTime between #{beginTime} and #{endTime})" +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            " order by ob.CreateTime desc" +
            "</script>")
    List<OrderBusinessDTO> getListForServe(@Param("userCode") String userCode,
                                           @Param("beginTime") Date beginTime,
                                           @Param("endTime") Date endTime,
                                           @Param("name") String name,
                                           @Param("phone") String phone,
                                           @Param("status") int status);



    //商家订单 服务类商品
    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and BusinessCode=#{businessCode} and ob.OrderType=2 " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' isDelete !=-1 '>" +
            " and ob.isDelete=#{isDelete} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO>  getListForServiceOrder(@Param("beginTime") Date beginTime,
                                                   @Param("endTime") Date endTime,
                                                   @Param("businessCode") String businessCode,
                                                   @Param("name") String name,
                                                   @Param("phone") String phone,
                                                   @Param("status") int status,
                                                   @Param("isDelete")int isDelete);


    @Select("<script>" +
            "select  COALESCE(sum(ob.Price),0) from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and BusinessCode=#{businessCode} and ob.OrderType=2 " +
            "<if test =' name !=\"\" '>" +
            " and u.Name = #{name} " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone = #{phone} " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' isDelete !=-1 '>" +
            " and ob.isDelete=#{isDelete} " +
            "</if>" +
            "</script>")
    double getServeOrderBusinessPrice(@Param("beginTime") Date beginTime,
                                       @Param("endTime") Date endTime,
                                       @Param("businessCode") String businessCode,
                                       @Param("name") String name,
                                       @Param("phone") String phone,
                                       @Param("status") int status,
                                       @Param("isDelete") int isDelete);


    //商家订单 服务类商品(管理员)
    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and ob.OrderType=2  and o.GovernmentCode=#{governmentCode} " +
            " and ob.IsDelete = 0 " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO>  getListForServiceOrderAdm(@Param("beginTime") Date beginTime,
                                                   @Param("endTime") Date endTime,
                                                   @Param("name") String name,
                                                   @Param("phone") String phone,
                                                   @Param("status") int status,
                                                      @Param("governmentCode") String governmentCode);








    //商家订单 普通商品
    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and BusinessCode=#{businessCode} and ob.OrderType=1 " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' isDelete !=-1 '>" +
            " and ob.isDelete=#{isDelete} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO>  getListForCommonOrder(@Param("beginTime") Date beginTime,
                                                  @Param("endTime") Date endTime,
                                                  @Param("businessCode") String businessCode,
                                                  @Param("name") String name,
                                                  @Param("phone") String phone,
                                                  @Param("status") int status,
                                                  @Param("isDelete")int isDelete);

    @Select("<script>" +
            "select  sum(ob.Price) from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and BusinessCode=#{businessCode} and ob.OrderType=1 " +
            "<if test =' name !=\"\" '>" +
            " and u.Name = #{name} " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone = #{phone} " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' isDelete !=-1 '>" +
            " and ob.isDelete=#{isDelete} " +
            "</if>" +
            "</script>")
    double getCommonOrderBusinessPrice(@Param("beginTime") Date beginTime,
                                        @Param("endTime") Date endTime,
                                        @Param("businessCode") String businessCode,
                                        @Param("name") String name,
                                        @Param("phone") String phone,
                                        @Param("status") int status,
                                        @Param("isDelete") int isDelete);

    //商家订单 收银机订单
    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join elderly e on o.CashierElderlyCode = e.ElderlyCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and BusinessCode=#{businessCode} and ob.OrderType=1 and OrderFrom = 'table' " +
            "<if test =' name !=\"\" and name!=null '>" +
            " and e.Name = #{name} " +
            "</if>" +
            "<if test =' phone !=\"\" and phone!=null '>" +
            " and e.Phone = #{phone} " +
            "</if>" +
            "<if test =' cashierCode !=\"\" and cashierCode!=null '>" +
            " and o.UserCode = #{cashierCode} " +
            "</if>" +
            "<if test =' isDelete !=-1 '>" +
            " and ob.isDelete=#{isDelete} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO>  getListForCashierOrder(@Param("beginTime") Date beginTime,
                                                  @Param("endTime") Date endTime,
                                                  @Param("businessCode") String businessCode,
                                                  @Param("name") String name,
                                                  @Param("phone") String phone,
                                                  @Param("isDelete") int isDelete,
                                                   @Param("cashierCode") String cashierCode);

    @Select("<script>" +
            "select  COALESCE(sum(ob.Price),0) from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join elderly e on o.CashierElderlyCode = e.ElderlyCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and BusinessCode=#{businessCode} and ob.OrderType=1 and OrderFrom = 'table' " +
            "<if test =' name !=\"\" and name!=null '>" +
            " and e.Name = #{name} " +
            "</if>" +
            "<if test =' phone !=\"\" and phone!=null '>" +
            " and e.Phone = #{phone} " +
            "</if>" +
            "<if test =' cashierCode !=\"\" and cashierCode!=null '>" +
            " and o.UserCode = #{cashierCode} " +
            "</if>" +
            "<if test =' isDelete !=-1 '>" +
            " and ob.isDelete=#{isDelete} " +
            "</if>" +
            "</script>")
    double getCashierOrderBusinessPrice(@Param("beginTime") Date beginTime,
                                        @Param("endTime") Date endTime,
                                        @Param("businessCode") String businessCode,
                                        @Param("name") String name,
                                        @Param("phone") String phone,
                                        @Param("isDelete") int isDelete,
                                        @Param("cashierCode") String cashierCode);


    //商家订单 普通商品(管理员)
    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            " and ob.OrderType=1  and o.GovernmentCode=#{governmentCode}   " +
            " and ob.IsDelete = 0 " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO>  getListForCommonOrderAdm(@Param("beginTime") Date beginTime,
                                                  @Param("endTime") Date endTime,
                                                  @Param("name") String name,
                                                  @Param("phone") String phone,
                                                  @Param("status") int status,
                                                     @Param("governmentCode") String governmentCode);

    //大于发货日期10天的订单自动确认收货
    @Select("SELECT b.* FROM order_business b,order_logistics l where " +
            "b.Status=#{status} " +
            "and b.OrderBusinessCode = l.OrderBusinessCode " +
            "and DATEDIFF(NOW(),l.SendTime) >=1 ")
    List<OrderBusiness> getByStatus(int status);

    //自动取消未支付订单
    @Select("SELECT * FROM order_business o " +
            "WHERE " +
            "`Status` IN (0,10) " +
            "AND DATEDIFF(NOW(),o.CreateTime) >=1")
    List<OrderBusiness> getAutoCanceOrderByStatus();


    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            " and ob.OrderType=1  and o.GovernmentCode=#{governmentCode}   " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' businessCode !=\"\" and businessCode!=null '>" +
            " and ob.BusinessCode =#{businessCode} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO> getCommonOrderToGovPlatform(@Param("beginTime") Date beginTime,
                                                       @Param("endTime") Date endTime,
                                                       @Param("name") String name,
                                                       @Param("phone") String phone,
                                                       @Param("status") int status,
                                                       @Param("governmentCode") String governmentCode,
                                                       @Param("businessCode")String businessCode);

    @Select("<script>" +
            "select  * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode " +
            "left join user u on o.UserCode = u.UserCode  where " +
            "(ob.CreateTime between #{beginTime} and #{endTime}) " +
            "and ob.OrderType=2  and o.GovernmentCode=#{governmentCode} " +
            "<if test =' name !=\"\" '>" +
            " and u.Name like concat('%',#{name},'%') " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and u.Phone like concat('%',#{phone},'%') " +
            "</if>" +
            "<if test =' status !=-1 '>" +
            " and ob.Status=#{status} " +
            "</if>" +
            "<if test =' businessCode !=\"\" and businessCode!=null '>" +
            " and ob.BusinessCode =#{businessCode} " +
            "</if>" +
            " order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO> getServiceOrderToGovPlatform(@Param("beginTime") Date beginTime,
                                                        @Param("endTime") Date endTime,
                                                        @Param("name") String name,
                                                        @Param("phone") String phone,
                                                        @Param("status") int status,
                                                        @Param("governmentCode") String governmentCode,
                                                        @Param("businessCode")String businessCode);

    @Select("<script>" +
            "select * from order_business as ob " +
            "left join `order` o on ob.OrderCode = o.OrderCode  " +
            "left join elderly e on o.UserCode = e.UserCode " +
            "where (ob.CreateTime between #{startTime} and #{endTime} ) " +
            "and ob.Status in (14,16,3,7) " +
            "and o.GovernmentCode=#{governmentCode} " +
            "<if test =' name !=\"\" '>" +
            " and e.Name=#{name} " +
            "</if>" +
            "<if test =' phone !=\"\" '>" +
            " and e.Phone=#{phone} " +
            "</if>" +
            "<if test =' orderType !=-1 '>" +
            " and ob.OrderType=#{orderType} " +
            "</if>" +
            "<if test =' businessCode !=\"\" and businessCode!=null '>" +
            " and ob.BusinessCode =#{businessCode} " +
            "</if>" +
            "</script>")
    List<OrderBusinessDTO> getForWaterForGov(@Param("startTime") String startTime,
                                             @Param("endTime") String endTime,
                                             @Param("governmentCode") String governmentCode,
                                             @Param("name") String name,
                                             @Param("phone") String phone,
                                             @Param("businessCode") String businessCode,
                                             @Param("orderType")int orderType);




    //服务类订单  消费用户统计
    @Select(" select  distinct  UserCode from `order`   " +
            "where GovernmentCode=#{governmentCode} and  OrderCode  in  " +
            "(select distinct OrderCode from order_business  " +
            "where Status in(14,16)  " +
            "and OrderType=2 " +
            "and ( CreateTime between #{startTime} and #{endTime} ) ) ")
    List<UserDTO2> getUserForConsumption(@Param("startTime") Date startTime,
                                         @Param("endTime") Date endTime,
                                         @Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select * from order_business ob left join `order` o on ob.OrderCode=o.OrderCode " +
            "where o.UserCode=#{userCode} " +
            "<if test =' businessCode !=\"\" and businessCode!=null '>" +
            " and ob.BusinessCode =#{businessCode} " +
            "</if>" +
            "<if test =' orderType !=-1 '>" +
            "and ob.OrderType=#{orderType} " +
            "</if>" +
            "and ob.Status in(3,7,14,16) " +
            "and ob.CreateTime between #{startTime} and #{endTime} " +
            "order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO> userConsumerOrderInfo(@Param("userCode")String userCode,@Param("startTime")String startTime,@Param("endTime")String endTime,
                                                 @Param("orderType")int orderType,@Param("businessCode")String businessCode);




    @Select("<script>" +
            "select * from order_business ob " +
            "where ob.BusinessCode =#{businessCode} " +
            "<if test =' orderType !=-1 '>" +
            "and ob.OrderType=#{orderType} " +
            "</if>" +
            "and ob.Status in(3,7,14,16) " +
            "and ob.CreateTime between #{startTime} and #{endTime} " +
            "order by ob.CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO> businessConsumerOrderInfo(@Param("startTime")String startTime,@Param("endTime")String endTime,
                                                     @Param("orderType")int orderType,@Param("businessCode")String businessCode);



    @Select("<script>" +
            "select OrderBusinessCode from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where YEAR(ob.CreateTime)=YEAR(NOW()) " +
            " and o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7) " +
            "</script>")
    List<OrderBusiness> getOrderNowYearOrderNew(@Param("governmentCode") String governmentCode);



    @Select("<script>" +
            "select OrderBusinessCode from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where YEAR(ob.CreateTime) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR))" +
            " and o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7) " +
            "</script>")
    List<OrderBusiness> getOrderLastYearOrderNew(@Param("governmentCode") String governmentCode);



    @Select("<script>" +
            "select OrderBusinessCode from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where DATE_FORMAT(ob.CreateTime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') " +
            " and o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7)  " +

            "</script>")
    List<OrderBusiness> getOrderNowMonthsOrderNew(@Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select OrderBusinessCode from order_business as ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where DATE_FORMAT(ob.CreateTime, '%Y%m') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y%m') " +
            " and o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7) " +
            "</script>")
    List<OrderBusiness> getOrderLastMonthsOrderNew(@Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select  sum(ob.Price)  from  order_business ob left join `order` o on o.OrderCode=ob.OrderCode  " +
            "where YEAR(ob.CreateTime) = YEAR(NOW()) " +
            "and o.GovernmentCode=#{governmentCode}  " +
            "and ob.Status in (14,16,3,7) " +
            "</script>")
    Double getMoneyNowYearMoney(@Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select  sum(ob.Price)  from  order_business ob left join `order` o on o.OrderCode=ob.OrderCode " +
            "where YEAR(ob.CreateTime) = YEAR(DATE_SUB(NOW(), INTERVAL 1 YEAR)) " +
            "and o.GovernmentCode=#{governmentCode}  " +
            "and ob.Status in (14,16,3,7) " +
            "</script>")
    Double getMoneyLastYearMoney(@Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select sum(ob.Price) from order_business  ob left join `order` o on o.OrderCode=ob.OrderCode " +
            " where DATE_FORMAT(ob.CreateTime, '%Y%m') = DATE_FORMAT(CURDATE(), '%Y%m') " +
            " and o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7)  " +
            "</script>")
    Double getMoneyNowMonthsMoney(@Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select sum(ob.Price) from order_business ob left join `order` o on o.OrderCode=ob.OrderCode " +
            " where DATE_FORMAT(ob.CreateTime, '%Y%m') = DATE_FORMAT(DATE_SUB(curdate(), INTERVAL 1 MONTH), '%Y%m') " +
            " and o.GovernmentCode=#{governmentCode}" +
            " and ob.Status in (14, 16,3,7) " +
            "</script>")
    Double getMoneyLastMonthsMoney(@Param("governmentCode") String governmentCode);


    @Select("<script>" +
            "select  sum(ob.Price)  from  order_business ob " +
            "where YEAR(ob.CreateTime) = YEAR(NOW()) " +
            "and ob.BusinessCode=#{businessCode}  " +
            "and ob.Status in (14,16,3,7) " +
            "</script>")
    Double getBusinessNowYearMoney(@Param("businessCode") String businessCode);

    //获取n天前支付宝微信付款完成订单总额
    @Select("select cast(COALESCE(SUM(Price),0) AS decimal(18,2)) sumPrice from order_business where" +
            " BusinessCode=#{businessCode} and date(CompleteTime) = date_sub(curdate(),interval #{day} day) " +
            "and PayType in ('ali','wx') and Status in (14, 16,3,7)")
    Double getBusinessDayMoney(@Param("businessCode") String businessCode,@Param("day") int day);

    //获取支付宝微信付款完成订单列表
    @Select("<script>" +
            "select * from order_business where" +
            " BusinessCode=#{businessCode}" +
            "<if test=' orderType !=-1 '>" +
            " and OrderType=#{orderType} " +
            "</if>" +
            " and date(CompleteTime) = #{dayTime} " +
            "and PayType in ('ali','wx') and Status in (14, 16,3,7) " +
            "order by CreateTime desc " +
            "</script>")
    List<OrderBusinessDTO> getBusinessDayList(@Param("businessCode") String businessCode, @Param("dayTime") String dayTime, @Param("orderType") int orderType);


    //同步方法收银机下单状态完成时间
    @Select(" UPDATE order_business set `Status`=3,CompleteTime=CreateTime WHERE `Status`=1 and OrderFrom='table'")
    int updateOrderBusinessStatus();

    //同步所有完成订单完成时间
    @Select("UPDATE order_business set CompleteTime=PayTime WHERE `Status` in (14, 16,3,7)")
    int updateOrderBusinessTime();

    //查询不同商家不同天数商家订单总额
    @Select("SELECT a.createTimes createTime,a.Price,a.BusinessCode FROM (select DATE_FORMAT(CompleteTime, '%Y-%m-%d') createTimes,cast(COALESCE(SUM(Price),0) AS decimal(18,2)) price,BusinessCode from order_business where PayType in ('ali','wx') and Status in (14, 16,3,7) GROUP BY createTimes,BusinessCode) a")
    List<AccountingRecord> getBusinessDayAllList();

    //通过商家名称查询出订单总数,支付方式等
    @Select("<script> " +
            "select count(ob.OrderBusinessCode) " +
            "from `order` o LEFT JOIN order_business as ob on o.OrderCode = ob.OrderCode " +
            "where ob.PayType = #{paytype}   " +
            "and ob.CreateTime between #{startTime} and #{endTime} " +
            "<if test = 'businessCode !=null and businessCode!= \"\"'>" +
            "and ob.BusinessCode = #{businessCode} " +
            "</if>" +
            "and ob.Status in (11,12,13,14,16) " +
            "GROUP BY PayType " +
            "</script>")
    int getListStatis(@Param("startTime") String startTime,
                      @Param("endTime") String endTime,
                      @Param("businessCode") String businessCode,
                      @Param("paytype") String PayType);

    //查询商家中的客户付款方式
    @Select("<script> select * from order_business " +
            "where Status in (11,12,13,14,16)" +
            "<if test = 'businessCode !=null and businessCode!= \"\"'>" +
            "and businessCode=#{businessCode} " +
            "</if>" +
            "and CreateTime between #{startTime} and #{endTime} " +
            "GROUP BY PayType" +
            "</script>")
    List<OrderStatisticsDTO> getPayType(@Param("startTime") String startTime,
                                        @Param("endTime") String endTime,
                                        @Param("businessCode") String businessCode);


    //通过商家名称查询出支付总额
    @Select("<script> select COALESCE(sum(ob.Price),0) " +
            "from `order` o LEFT JOIN order_business as ob on o.OrderCode = ob.OrderCode " +
            "where ob.PayType = #{paytype}  " +
            "and ob.CreateTime between #{startTime} and #{endTime} " +
            "<if test = 'businessCode !=null and businessCode!= \"\"'>" +
            "and ob.BusinessCode = #{businessCode} " +
            "</if>" +
            "and ob.Status in (11,12,13,14,16) " +
            "GROUP BY PayType " +
            "</script>")
    double getListStatic(@Param("startTime") String startTime,
                         @Param("endTime") String endTime,
                         @Param("businessCode") String businessCode,
                         @Param("paytype") String PayType);



    //商家昨天的订单
    @Select("select OrderBusinessCode from order_business" +
            " where businessCode= #{businessCode}  " +
            " and CreateTime between #{startTime} and #{endTime} ")
    List<OrderBusiness> ListYesterday(@Param("businessCode") String businessCode,
                                      @Param("startTime") Date startTime,
                                      @Param("endTime") Date endTime);





}
